<?php

namespace app\admin\logic\Order;

use app\admin\model\Order as OrderModel;
use app\common\ExcelService;
use think\facade\Db;
use think\facade\Log;

class ImporAndExport
{
    /**
     * @describe 导出订单数据
     * @author: Bobo
     * @date: 2025-09-03 13:36:08 
     * @param [type] @required
     * @return array
     */
    public function export()
    {
        $OrderModel = new OrderModel();
        $order_attr = $OrderModel->getStaticAttr();
        $start_time = '2025-08-01 00:00:00';
        $end_time = '2025-09-30 23:59:59';

        $order_field = 'o.id,o.user_id,o.order_no,o.status,o.pay_status,o.exceptionstatus,o.id_number,o.order_type,o.source,o.delivery_method, o.country_name,concat(o.first_name,o.last_name) as delivery_name, o.phone,concat(o.state,o.city_name,o.address,o.address2) as delivery_address,o.signatory,o.signing_date, o.contract_signing_status, o.owner, o.single_order_method,o.partner,o.product_total_amount,o.logistics_fee,o.total_amount,o.pay_amount,o.notes,o.pay_terms,o.payment_deadline,o.pay_method_name,o.currency,o.shipping_start_date,o.shipping_end_date,o.creator,o.create_time,o.updater,o.update_time,o.pay_time as first_pay_time,o.last_delivery_time,o.order_category';
        $orderList = Db::name('order')
            ->alias('o')
            ->rightJoin('order_item oi', 'oi.order_id = o.id')
            ->field($order_field . ',
                oi.id as oi_id, oi.product_id, oi.first_leg_logistics_fee, oi.warehouse_id, oi.is_futures,oi.original_price,oi.actual_price,oi.price_selected_currency,oi.quantity,oi.arrival_date,oi.transaction_currency_id')
            ->where('o.is_del', 0)
            ->where('oi.is_del', 0)
            ->where('oi.quantity', '>', 0)
            ->where('o.order_category', 'wholesal')
            ->where(function ($query) use ($start_time, $end_time) {
                $query->where(function ($subQuery) use ($start_time, $end_time) {
                    $subQuery->whereIn('o.pay_status', ['3', '4'])
                        ->where('o.id', '<', 400000)
                        ->whereBetween('o.pay_time', [$start_time, $end_time]);
                })->whereOr(function ($subQuery) use ($start_time, $end_time) {
                    $subQuery->whereIn('o.status', ['50', '100']);
                    // ->whereBetween('o.last_delivery_time', [date('Y-m-d', strtotime($start_time)), date('Y-m-d', strtotime($end_time))]);
                });
            })
            ->select()
            ->toArray();

        $all_user_ids = array_unique(array_column($orderList, 'user_id'));
        $all_user_list = Db::name('customer')->where('id', 'in', $all_user_ids)->column('id,cusname', 'id');

        $operator_list = Db::name('operator')->column('id,cname', 'id');

        $all_currency_ids = array_unique(array_column($orderList, 'currency'));
        $all_currency_list = Db::name('transaction_currency')->where('id', 'in', $all_currency_ids)->column('id,name', 'id');

        $all_product_ids = array_unique(array_column($orderList, 'product_id'));
        $all_product_list = Db::name('product')->where('id', 'in', $all_product_ids)->column('id,code,billing_weight', 'id');

        // 调试：检查是否有产品ID不存在
        $missing_product_ids = array_diff($all_product_ids, array_keys($all_product_list));
        if (!empty($missing_product_ids)) {
            Log::error("缺失的产品ID: " . implode(',', $missing_product_ids));
        }

        $all_warehouse_ids = array_unique(array_column($orderList, 'warehouse_id'));
        $all_warehouse_list = Db::name('warehouse')->where('id', 'in', $all_warehouse_ids)->column('id,name', 'id');



        // 获取现货商品的发货数量
        $deliveryProduct = $this->getDeliveryNumber($orderList);
        // 获取所有商品的折扣价格
        $discountPrice = $this->getDiscountPrice($all_product_ids, $all_warehouse_ids);

        $logistics_fee_list = [];
        foreach ($orderList as $item) {
            if ($item['logistics_fee'] > 0 && isset($all_product_list[$item['product_id']])) {
                $billing_weight = $item['quantity'] * ($all_product_list[$item['product_id']]['billing_weight'] ?? 0);
                if (!isset($logistics_fee_list[$item['id']])) {
                    $logistics_fee_list[$item['id']]['logistics_fee'] = $item['logistics_fee'];
                    $logistics_fee_list[$item['id']]['billing_weight'] = $billing_weight;
                } else {
                    $logistics_fee_list[$item['id']]['billing_weight'] += $billing_weight;
                }
            }
        }

        $all_order_ids = array_unique(array_column($orderList, 'id'));
        $all_payment_list = Db::name('order_payment')
            ->where('order_id', 'in', $all_order_ids)
            ->where('status', '01')
            ->where('pay_type', '00')
            ->field('MAX(pay_time) as pay_time,order_id')
            ->group('order_id')
            ->select()
            ->toArray();
        $all_payment_list = array_column($all_payment_list, 'pay_time', 'order_id');


        foreach ($orderList as $key => &$value) {

            $discount_level = $this->getDiscountPriceLevel($value, $discountPrice);
            $value['discount_level'] = $discount_level[1];
            $value['is_discount'] = $discount_level[0];
            $value['order_no'] = ' ' . ($value['order_no']);

            $value['order_category'] = $value['order_category'] == 'order' ? '普通订单' : '资产订单';

            $value['user_id'] = isset($all_user_list[$value['user_id']]) ? $all_user_list[$value['user_id']]['cusname'] : '';
            $value['status'] = isset($order_attr['status'][$value['status']]) ? $order_attr['status'][$value['status']] : '';
            $value['pay_status'] = isset($order_attr['pay_status'][$value['pay_status']]) ? $order_attr['pay_status'][$value['pay_status']] : '';
            $value['order_type'] = isset($order_attr['order_type'][$value['order_type']]) ? $order_attr['order_type'][$value['order_type']] : '';
            $value['source'] = isset($order_attr['source'][$value['source']]) ? $order_attr['source'][$value['source']] : '';
            $value['delivery_method'] = isset($order_attr['delivery_method'][$value['delivery_method']]) ? $order_attr['delivery_method'][$value['delivery_method']] : '';
            $value['contract_signing_status'] = isset($order_attr['contract_signing_status'][$value['contract_signing_status']]) ? $order_attr['contract_signing_status'][$value['contract_signing_status']] : '';
            $value['pay_terms'] = isset($order_attr['pay_terms'][$value['pay_terms']]) ? $order_attr['pay_terms'][$value['pay_terms']] : '';
            $value['is_futures'] = isset($order_attr['is_futures'][$value['is_futures']]) ? $order_attr['is_futures'][$value['is_futures']] : '';
            $value['currency'] = isset($all_currency_list[$value['currency']]) ? $all_currency_list[$value['currency']]['name'] : '';
            $value['owner'] = isset($operator_list[$value['owner']]) ? $operator_list[$value['owner']]['cname'] : '';
            $value['signatory'] = isset($operator_list[$value['signatory']]) ? $operator_list[$value['signatory']]['cname'] : '';
            $value['creator'] = isset($operator_list[$value['creator']]) ? $operator_list[$value['creator']]['cname'] : '';
            $value['updater'] = isset($operator_list[$value['updater']]) ? $operator_list[$value['updater']]['cname'] : '';

            $value['delivery_number'] = isset($deliveryProduct[$value['id'] . '_' . $value['product_id']]) ? $deliveryProduct[$value['id'] . '_' . $value['product_id']] : 0;

            $value['last_pay_time'] = isset($all_payment_list[$value['id']]) ? $all_payment_list[$value['id']] : '';

            $value['exceptionstatus'] = $value['exceptionstatus'] ? explode(',', $value['exceptionstatus']) : [];

            foreach ($value['exceptionstatus'] as $key => $v) {
                $value['exceptionstatus'][$key] = isset($order_attr['exceptionstatus'][$v]) ? $order_attr['exceptionstatus'][$v] : '';
            }
            $value['exceptionstatus'] = $value['exceptionstatus'] ? implode(',', $value['exceptionstatus']) : '';

            $value['partner'] = $value['partner'] ? explode(',', $value['partner']) : [];
            foreach ($value['partner'] as $key => $v) {
                $value['partner'][$key] = isset($operator_list[$v]) ? $operator_list[$v]['cname'] : '';
            }
            $value['partner'] = $value['partner'] ? implode(',', $value['partner']) : '';

            if (isset($logistics_fee_list[$value['id']]) && $logistics_fee_list[$value['id']]['billing_weight'] > 0) {


                $value['logistics_fee'] = $logistics_fee_list[$value['id']]['logistics_fee'] / $logistics_fee_list[$value['id']]['billing_weight'] * $value['quantity'] * ($all_product_list[$value['product_id']]['billing_weight'] ?? 0);
                $value['logistics_fee'] = number_format($value['logistics_fee'], 2);
                $value['total_logistics_fee'] = $logistics_fee_list[$value['id']]['logistics_fee'];
            } else {
                $value['logistics_fee'] = 0;
                $value['total_logistics_fee'] = 0;
            }

            $value['product_id'] = isset($all_product_list[$value['product_id']]) ? $all_product_list[$value['product_id']]['code'] : '';
            $value['warehouse_id'] = isset($all_warehouse_list[$value['warehouse_id']]) ? $all_warehouse_list[$value['warehouse_id']]['name'] : '';


            unset($value['op_id']);
        }
        $header = [
            '序号',
            '订单编码',
            '客户名称',
            '付款状态',
            '订单状态',
            '异常状态',
            '发货仓库',
            '商品名称',
            '货物类型（现货）',
            '单台建单时报价单零售价',
            '单台折扣价',
            '单台真实售价',
            '特殊协商价',
            '销售数量',
            '已发货数量',
            '阶梯价格',
            '社会信用代码',
            '普通/资产',
            '订单类型',
            '来源',
            '收货方式',
            '收货国家',
            '收货人',
            '联系电话',
            '收货地址',
            '签订人',
            '签订日期',
            '合同签署状态',
            '负责人',
            '成单方式',
            '合作人',
            '产品合计',
            '产品物流分摊',
            '物流费用',
            '头程物流费',
            '订单总金额',
            '付款金额',
            '买家留言',
            '支付条件',
            '收款截止日期',
            '支付类型',
            '付款币种',
            '发货起始日期',
            '发货截止时间',
            '创建人',
            '创建时间',
            '第一次付款时间',
            '最后一次付款时间',
            '修改人',
            '修改时间'
        ];

        // 定义需要导出的字段，按顺序排列
        $exportFields = [
            'id',
            'order_no',
            'user_id',
            'pay_status',
            'status',
            'exceptionstatus',
            'warehouse_id',
            'product_id',
            'is_futures',
            'original_price',
            'actual_price',
            'price_selected_currency',
            'is_discount',
            'quantity',
            'delivery_number',
            'discount_level',
            'id_number',
            'order_category',
            'order_type',
            'source',
            'delivery_method',
            'country_name',
            'delivery_name',
            'phone',
            'delivery_address',
            'signatory',
            'signing_date',
            'contract_signing_status',
            'owner',
            'single_order_method',
            'partner',
            'product_total_amount',
            'logistics_fee',
            'total_logistics_fee',
            'first_leg_logistics_fee',
            'total_amount',
            'pay_amount',
            'notes',
            'pay_terms',
            'payment_deadline',
            'pay_method_name',
            'currency',
            'shipping_start_date',
            'shipping_end_date',
            'creator',
            'create_time',
            'first_pay_time',
            'last_pay_time',
            'updater',
            'update_time'
        ];

        $orderData = [];
        foreach ($orderList as $key => $item) {
            $orderData[$key] = [];
            foreach ($exportFields as $field) {
                $orderData[$key][] = $item[$field] ?? '';
            }
        }





        $file_name = date('Ym', strtotime('-1 month')) . '_order_export.xlsx';
        $file_path = getcwd() . DIRECTORY_SEPARATOR . 'runtime' . DIRECTORY_SEPARATOR . 'exports' . DIRECTORY_SEPARATOR . $file_name;

        // 确保目录存在
        $export_dir = dirname($file_path);
        if (!is_dir($export_dir)) {
            mkdir($export_dir, 0755, true);
        }

        // 如果文件已存在，先删除
        if (file_exists($file_path)) {
            unlink($file_path);
        }

        // 保存到文件
        $result = ExcelService::export($orderData, $header, $file_name, true, $file_path);

        if (file_exists($file_path)) {
            echo "Excel文件已生成: " . $file_path . PHP_EOL;
        } else {
            echo "错误：文件生成失败" . PHP_EOL;
        }

        return $orderList;
    }

    /**
     * @describe 获取现货商品的发货数量
     * @author: Bobo
     * @date: 2025-09-03 13:36:08 
     * @param [type] @required
     * @return array
     */
    private function getDeliveryNumber($orderList)
    {
        $spotProductIds = [];
        $spotOrderIds = [];
        foreach ($orderList as $deliveryItem) {
            if ($deliveryItem['is_futures'] == 0) {
                $spotProductIds[] = $deliveryItem['product_id'];
                $spotOrderIds[] = $deliveryItem['id'];
            }
        }
        $spotProductIds = array_unique($spotProductIds);
        $spotOrderIds = array_unique($spotOrderIds);

        $deliveryProductData = Db::name('logistics_order_item')
            ->alias('a')
            ->join('logistics_order b', 'a.logistics_order_id = b.id')
            ->where('b.status', 1)
            ->where('b.type', 1)
            ->where('a.product_id', 'in', $spotProductIds)
            ->where('a.order_id', 'in', $spotOrderIds)
            ->field('a.order_id,a.product_id,SUM(a.number) as total_number')
            ->group('a.order_id,a.product_id')
            ->select()
            ->toArray();
        // 转换为以 order_id_product_id 为键的关联数组
        $deliveryProduct = [];
        foreach ($deliveryProductData as $item) {
            $key = $item['order_id'] . '_' . $item['product_id'];
            $deliveryProduct[$key] = $item['total_number'];
        }
        return $deliveryProduct;
    }

    /**
     * @describe 获取所有商品的折扣价格
     * @author: Bobo
     * @date: 2025-09-03 13:36:08 
     * @param [type] @required
     * @return array
     */
    private function getDiscountPrice($all_product_ids, $all_warehouse_ids)
    {
        $productPriceData = Db::name('inventory')
            ->alias('a')
            ->join('product_price b', 'a.id = b.inventory_id and a.wid = b.wid')
            ->leftJoin('product_discount c', 'b.id = c.price_id')
            ->where('a.productid', 'in', $all_product_ids)
            ->where('a.wid', 'in', $all_warehouse_ids)
            ->where('a.is_del', 0)
            ->where('b.is_del', 0)
            ->field('a.productid,a.wid,a.is_futures,a.arrival_date,b.transaction_currency_id,b.price as original_price,c.level,c.num,c.rate,c.price as discount_price,b.id as price_id')
            ->order('a.productid,a.wid,a.is_futures,a.arrival_date,b.transaction_currency_id,c.level ASC')
            ->select()
            ->toArray();


        $discountPrice = [];
        foreach ($productPriceData as $item) {
            $key = $item['productid'] . '_' . $item['wid'] . '_' . $item['is_futures'] . '_' . $item['arrival_date'] . '_' . $item['transaction_currency_id'];

            // 初始化该产品的价格信息
            if (!isset($discountPrice[$key])) {
                $discountPrice[$key] = [
                    'price' => $item['original_price'],
                    'discount_price' => []
                ];
            }

            // 只有当存在折扣信息时才添加到折扣数组中
            if ($item['level'] !== null && $item['discount_price'] !== null) {
                $discountPrice[$key]['discount_price'][$item['level']] = [
                    'level' => $item['level'],
                    'num' => $item['num'],
                    'rate' => $item['rate'],
                    'discount_price' => $item['discount_price']
                ];
            }
        }
        return $discountPrice;
    }

    /**
     * @describe 获取当前商品的折扣级别
     * @author: Bobo
     * @date: 2025-09-03 13:36:08 
     * @param [type] @required
     * @return array
     */
    private function getDiscountPriceLevel($item, $discountPrice)
    {
        $key = $item['product_id'] . '_' . $item['warehouse_id'] . '_' . $item['is_futures'] . '_' . $item['arrival_date'] . '_' . $item['transaction_currency_id'];
        $actual_price = floatval($item['actual_price']);

        // 检查是否存在对应的产品价格数据
        if (!isset($discountPrice[$key])) {
            return ['否', '原价']; // 返回原价
        }

        $productData = $discountPrice[$key];


        // 如果没有折扣价格数据，返回原价
        if (empty($productData['discount_price'])) {
            return ['否', '原价'];
        }

        // 按level从小到大排序折扣价格
        $discounts = $productData['discount_price'];
        ksort($discounts); // 按level排序

        // 根据actual_price匹配对应的level
        $matched_level = null;
        $previous_discount_price = null;

        foreach ($discounts as $level => $discount_info) {
            $discount_price = floatval($discount_info['discount_price']);

            if ($level == 1) {
                // level为1的情况：actual_price >= level1的discount_price
                if ($actual_price >= $discount_price) {
                    $matched_level = 1;
                }
                $previous_discount_price = $discount_price;
            } else {
                // level为2及以上的情况：actual_price >= 当前level的discount_price && actual_price < 上一个level的discount_price
                if ($actual_price >= $discount_price && $actual_price < $previous_discount_price) {
                    $matched_level = $level;
                    break; // 找到匹配的level就退出
                }
                $previous_discount_price = $discount_price;
            }
        }

        // 如果找到匹配的level，返回 "level + 级阶梯价" 格式，否则返回 "原价"
        if ($matched_level !== null && isset($discounts[$matched_level])) {

            $matched_level_number = $discounts[$matched_level]['num'];
            if ($item['quantity'] >= $matched_level_number) {
                return ['否', $matched_level . '级阶梯价'];
            } else {
                return ['是', $matched_level . '级阶梯价'];
            }
        }

        return ['否', '原价']; // 没有匹配的level，返回原价
    }
}
